#! /usr/bin/env python
from sqlite3 import dbapi2 as sqlite

flist = ["Date", "First", "Second", "Third", "Fourth", "Fifth","Powerball"]

conn = sqlite.connect("lotto-hist")
conn.row_factory = sqlite.Row
curs = conn.cursor()
conn.text_factory = sqlite.OptimizedUnicode

def sort_history():
	for i in range(1,60):
		new_table = "whiteball_" + str(i)
		conn.execute('DROP TABLE IF EXISTS %s' %(new_table))
		conn.execute('CREATE TABLE  %s (dates_drawn  INT) ' %(new_table) )
		conn.commit()
		curs.execute("SELECT * FROM history WHERE first=? OR second=? OR third=? OR fourth=? OR fifth=? ORDER BY date",(i,i,i,i,i))
		
		conn.text_factory = str
		rows = curs.fetchall()
		
		for row in rows:
			print "Sorting number: " + str(i)
			curs.execute('select pkindex from dates where date = ?', [row[0]])
			date_index = curs.fetchone()
			#print date_index[0]
			curs.execute('INSERT INTO %s VALUES (?)' %(new_table), [date_index[0]])
			conn.commit()
		
		skip_table = "skips_" + str(i)
		conn.execute('drop table if exists %s' %(skip_table))
		conn.execute('create table %s(skip_number int, skip_count int)' %(skip_table))
		curs.execute('select * from %s' %(new_table))
		rows = curs.fetchall()
		previous_hit =0
		for row in rows:
			next_hit = row[0]
			skip_count = next_hit - previous_hit
			#print "skip count " + str(skip_count) + " for ball " + str(i)
			curs.execute('select skip_count from %s where skip_number = ?' %skip_table, [skip_count])
			old_skip_count =1
			skip_count_row = curs.fetchone()
			if skip_count_row ==None:
				curs.execute('insert into %s values(?,?)' %(skip_table),[skip_count, old_skip_count])
			else:
				#print skip_count_row
				old_skip_count = skip_count_row[0]
				old_skip_count += 1
				curs.execute('update  %s set skip_count=? where skip_number=? ' %(skip_table),[old_skip_count, skip_count])
			previous_hit = next_hit
			conn.commit()

#find the last draw and draw count
def get_last_row(table_name):
	curs.execute("Select * from %s" %table_name)
	rows = curs.fetchall()
	index = 0 
	for row in rows:
		last_row = row
		index += 1
	return last_row, index

#return date as a string
def get_draw_date(date_index):
	curs.execute("Select date from dates where pkindex = ?",[date_index])
	row = curs.fetchone()
	return row[0]

			
